package dataAccessLayer.factoryMySQL.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dataAccessLayer.abstractFactory.daos.DAOingredients;
import dataAccessLayer.dataBase.DBManager;
import dataAccessLayer.visualObject.VOingredient;

public class DAOingredientsMySQL implements DAOingredients
{
	private DBManager dbManager;
	
	public DAOingredientsMySQL(DBManager dbManager) 
	{
		this.dbManager = dbManager;
	}

	public int insertIngredient(VOingredient vOingredient) 
	{
		int code = -1;

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try
		{
			preparedStatement = connection.prepareStatement("Insert INTO ingredients (name,measureUnity) VALUES (?,?)");

			preparedStatement.setString(1, vOingredient.getName());
			preparedStatement.setInt(2, vOingredient.getMeasureUnity());

			preparedStatement.execute();

			preparedStatement = connection.prepareStatement("SELECT code FROM ingredients ORDER BY code DESC LIMIT 1");

			resultSet = preparedStatement.executeQuery();

			resultSet.next();

			code = resultSet.getInt(1);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return code;
	}
	
	public boolean updateIngredient(VOingredient vOingredient) 
	{
		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();
		
		try {
			preparedStatement = connection.prepareStatement("UPDATE ingredients SET name=?, measureUnity=? WHERE code=?");
			preparedStatement.setString(1,vOingredient.getName());
			preparedStatement.setInt(2, vOingredient.getMeasureUnity());
			preparedStatement.setInt(3, vOingredient.getCode());
			
			preparedStatement.execute();
			
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return false;
	}

	public boolean removeIngredient(VOingredient vOingredient) 
	{
		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();
		
		try {
			preparedStatement = connection.prepareStatement("DELETE from ingredients where code=?");
			preparedStatement.setInt(1, vOingredient.getCode());
			
			preparedStatement.execute();
			
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return false;
	}
	
	public ArrayList<VOingredient> listIngredients() 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resulSet=null;
		Connection connection = dbManager.getConnection();
		ArrayList<VOingredient> searchIngredients=new ArrayList<VOingredient>();
		
		
		try {
			preparedStatement = connection.prepareStatement("SELECT code,name,measureUnity FROM ingredients");			
			resulSet=preparedStatement.executeQuery();
			while(resulSet.next())
			{
				VOingredient search=new VOingredient();
				search.setCode(resulSet.getInt(1));
				search.setName(resulSet.getString(2));
				search.setMeasureUnity(resulSet.getInt(3));
				searchIngredients.add(search);
			}
			
			return searchIngredients;
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}	
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return null;
	}

	public VOingredient searchIngredientByCode(VOingredient vOingredient) 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resulSet = null;
		Connection connection = dbManager.getConnection();

		VOingredient ingredientSearch = null;

		try
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,measureUnity FROM ingredients WHERE code=?");

			preparedStatement.setInt(1, vOingredient.getCode());

			resulSet = preparedStatement.executeQuery();
			
			resulSet.next();

			ingredientSearch = new VOingredient();
			ingredientSearch.setCode( resulSet.getInt(1) );
			ingredientSearch.setName( resulSet.getString(2) );
			ingredientSearch.setMeasureUnity(resulSet.getInt(3));
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return ingredientSearch;
	}

	public VOingredient searchIngredientByName(VOingredient vOingredient) 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resulSet = null;
		Connection connection = dbManager.getConnection();

		VOingredient ingredientSearch = null;

		try
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,measureUnity FROM ingredients WHERE name=?");

			preparedStatement.setString(1, vOingredient.getName());

			resulSet = preparedStatement.executeQuery();
			
			resulSet.next();

			ingredientSearch = new VOingredient();
			ingredientSearch.setCode( resulSet.getInt(1) );
			ingredientSearch.setName( resulSet.getString(2) );
			ingredientSearch.setMeasureUnity(resulSet.getInt(3));
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return ingredientSearch;
	}
	
}
